import 'dart:developer' as developer;
import 'package:sqflite/sqflite.dart';
import 'package:path/path.dart';

class WebDatabaseService {
  static final WebDatabaseService _instance = WebDatabaseService._internal();
  factory WebDatabaseService() => _instance;
  WebDatabaseService._internal();
  
  // 添加表名常量
  static const String wordsTable = 'words';
  static const String questionsTable = 'questions';
  static const String notesTable = 'notes';
  static const String essayMaterialsTable = 'essay_materials';
  static const String checkInRecordsTable = 'check_ins';
  static const String aiChatMessagesTable = 'ai_chat_messages';
  static const String practiceRecordsTable = 'practice_records';
  static const String checkInGoalsTable = 'check_in_goals';
  
  Database? _database;
  bool _initialized = false;
  
  // 添加 init 方法作为 initDatabase 的别名
  Future<void> init() async {
    await initDatabase();
  }
  
  // 添加一个标志来跟踪初始化过程
  bool _isInitializing = false;
  
  Future<void> initDatabase() async {
    // 如果已经初始化完成，直接返回
    if (_initialized) {
      developer.log('WebDatabaseService: 数据库已初始化');
      return;
    }
    
    // 如果正在初始化中，等待初始化完成
    if (_isInitializing) {
      developer.log('WebDatabaseService: 数据库正在初始化中，等待完成');
      // 等待一段时间后再检查初始化状态
      await Future.delayed(const Duration(milliseconds: 100));
      if (!_initialized) {
        return initDatabase(); // 递归调用，直到初始化完成
      }
      return;
    }
    
    _isInitializing = true;
    
    try {
      developer.log('WebDatabaseService: 初始化Web平台数据库');
      final databasesPath = await getDatabasesPath();
      final path = join(databasesPath, 'web_study_treasure.db');
      
      _database = await openDatabase(
        path,
        version: 1,
        onCreate: _createDatabase,
        onUpgrade: _upgradeDatabase,
      );
      
      developer.log('WebDatabaseService: SQLite数据库初始化完成，路径: $path');
      
      _initialized = true;
      _isInitializing = false;
      
      developer.log('Web数据库服务初始化完成');
    } catch (e) {
      _isInitializing = false;
      developer.log('Web数据库服务初始化失败: $e', error: e);
      rethrow;
    }
  }

  Future<void> _createDatabase(Database db, int version) async {
    developer.log('WebDatabaseService: 创建SQLite数据库表');
    
    // 创建作文素材表
    await db.execute('''
      CREATE TABLE ${WebDatabaseService.essayMaterialsTable} (
        id INTEGER PRIMARY KEY AUTOINCREMENT,
        title TEXT NOT NULL,
        content TEXT NOT NULL,
        category TEXT,
        tags TEXT,
        source TEXT,
        image_url TEXT,
        created_at INTEGER NOT NULL,
        updated_at INTEGER NOT NULL
      )
    ''');
    
    // 创建签到表
    await db.execute('''
      CREATE TABLE ${WebDatabaseService.checkInRecordsTable} (
        id INTEGER PRIMARY KEY AUTOINCREMENT,
        date INTEGER UNIQUE NOT NULL,
        study_time INTEGER DEFAULT 0,
        words_learned INTEGER DEFAULT 0,
        questions_answered INTEGER DEFAULT 0,
        note TEXT,
        created_at INTEGER NOT NULL,
        goal_progress TEXT
      )
    ''');
    
    // 创建AI聊天消息表
    await db.execute('''
      CREATE TABLE ${WebDatabaseService.aiChatMessagesTable} (
        id INTEGER PRIMARY KEY AUTOINCREMENT,
        role TEXT NOT NULL,
        content TEXT NOT NULL,
        timestamp INTEGER NOT NULL
      )
    ''');
    
    // 创建签到目标表
    await db.execute('''
      CREATE TABLE ${WebDatabaseService.checkInGoalsTable} (
        id INTEGER PRIMARY KEY AUTOINCREMENT,
        title TEXT NOT NULL,
        description TEXT,
        target_value INTEGER NOT NULL,
        unit TEXT NOT NULL,
        is_active INTEGER DEFAULT 1,
        created_at INTEGER NOT NULL,
        updated_at INTEGER NOT NULL
      )
    ''');
    
    developer.log('WebDatabaseService: 所有表创建完成');
  }

  Future<void> _upgradeDatabase(Database db, int oldVersion, int newVersion) async {
    developer.log('WebDatabaseService: 升级数据库，版本: $oldVersion -> $newVersion');
    
    // 添加签到目标表
    await db.execute('''
      CREATE TABLE IF NOT EXISTS ${WebDatabaseService.checkInGoalsTable} (
        id INTEGER PRIMARY KEY AUTOINCREMENT,
        title TEXT NOT NULL,
        description TEXT,
        target_value INTEGER NOT NULL,
        unit TEXT NOT NULL,
        is_active INTEGER DEFAULT 1,
        created_at INTEGER NOT NULL,
        updated_at INTEGER NOT NULL
      )
    ''');
    
    // 更新签到表结构
     try {
       await db.execute('ALTER TABLE ${WebDatabaseService.checkInRecordsTable} ADD COLUMN study_time INTEGER DEFAULT 0');
       await db.execute('ALTER TABLE ${WebDatabaseService.checkInRecordsTable} ADD COLUMN words_learned INTEGER DEFAULT 0');
       await db.execute('ALTER TABLE ${WebDatabaseService.checkInRecordsTable} ADD COLUMN questions_answered INTEGER DEFAULT 0');
       await db.execute('ALTER TABLE ${WebDatabaseService.checkInRecordsTable} ADD COLUMN note TEXT');
       await db.execute('ALTER TABLE ${WebDatabaseService.checkInRecordsTable} ADD COLUMN created_at INTEGER');
       await db.execute('ALTER TABLE ${WebDatabaseService.checkInRecordsTable} ADD COLUMN goal_progress TEXT');
     } catch (e) {
       developer.log('WebDatabaseService: 更新签到表结构时出错: $e');
     }
  }

  // 通用数据库操作方法
  
  Future<int> insert(String table, Map<String, dynamic> data) async {
    await _ensureInitialized();
    
    try {
      developer.log('WebDatabaseService: 插入数据到表 $table');
      print('WebDatabaseService: 插入数据到表 $table，数据: $data');
      print('WebDatabaseService: 数据库初始化状态: $_initialized');
      print('WebDatabaseService: 数据库对象: ${_database != null ? "已创建" : "未创建"}');
      
      // 检查表是否存在
      final tables = await _database!.query('sqlite_master', 
          where: 'type = ? AND name = ?', 
          whereArgs: ['table', table]);
      print('WebDatabaseService: 表 $table 是否存在: ${tables.isNotEmpty}');
      
      if (tables.isEmpty) {
        print('WebDatabaseService: 表 $table 不存在，尝试创建表');
        // 如果是签到目标表，尝试创建
        if (table == WebDatabaseService.checkInGoalsTable) {
          await _database!.execute('''
            CREATE TABLE IF NOT EXISTS ${WebDatabaseService.checkInGoalsTable} (
              id INTEGER PRIMARY KEY AUTOINCREMENT,
              title TEXT NOT NULL,
              description TEXT,
              target_value INTEGER NOT NULL,
              unit TEXT NOT NULL,
              is_active INTEGER DEFAULT 1,
              created_at INTEGER NOT NULL,
              updated_at INTEGER NOT NULL
            )
          ''');
          print('WebDatabaseService: 创建表 $table 成功');
        }
      }
      
      final result = await _database!.insert(table, data);
      print('WebDatabaseService: 插入成功，返回ID: $result');
      return result;
    } catch (e, stackTrace) {
      developer.log('WebDatabaseService: 插入数据失败: $e', error: e);
      print('WebDatabaseService: 插入数据失败: $e');
      print('WebDatabaseService: 错误堆栈: $stackTrace');
      return -1; // 返回-1表示插入失败
    }
  }

  Future<List<Map<String, dynamic>>> getAll(String table) async {
    await _ensureInitialized();
    
    try {
      developer.log('WebDatabaseService: 获取表 $table 的所有数据');
      return await _database!.query(table);
    } catch (e) {
      developer.log('WebDatabaseService: 获取所有数据失败: $e', error: e);
      return [];
    }
  }
  
  Future<List<Map<String, dynamic>>> query(
    String table, {
    bool? distinct,
    List<String>? columns,
    String? where,
    List<Object?>? whereArgs,
    String? groupBy,
    String? having,
    String? orderBy,
    int? limit,
    int? offset,
  }) async {
    await _ensureInitialized();
    
    try {
      developer.log('WebDatabaseService: 查询表 $table 的数据');
      return await _database!.query(
        table,
        distinct: distinct,
        columns: columns,
        where: where,
        whereArgs: whereArgs,
        groupBy: groupBy,
        having: having,
        orderBy: orderBy,
        limit: limit,
        offset: offset,
      );
    } catch (e) {
      developer.log('WebDatabaseService: 查询数据失败: $e', error: e);
      return [];
    }
  }

  Future<Map<String, dynamic>?> getById(String table, int id) async {
    await _ensureInitialized();
    
    try {
      developer.log('WebDatabaseService: 通过ID获取数据，表: $table，ID: $id');
      
      final results = await _database!.query(
        table,
        where: 'id = ?',
        whereArgs: [id],
        limit: 1,
      );
      return results.isNotEmpty ? results.first : null;
    } catch (e) {
      developer.log('WebDatabaseService: 通过ID获取数据失败: $e', error: e);
      return null;
    }
  }

  Future<List<Map<String, dynamic>>> getByIndex(String table, String field, dynamic value) async {
    await _ensureInitialized();
    
    try {
      developer.log('WebDatabaseService: 通过字段获取数据，表: $table，字段: $field，值: $value');
      
      return await _database!.query(
        table,
        where: '$field = ?',
        whereArgs: [value],
      );
    } catch (e) {
      developer.log('WebDatabaseService: 通过字段获取数据失败: $e', error: e);
      return [];
    }
  }

  Future<int> count(String table, {String? where, List<dynamic>? whereArgs}) async {
    await _ensureInitialized();
    
    try {
      developer.log('WebDatabaseService: 计算表 $table 中的记录数');
      
      final result = await _database!.rawQuery(
        'SELECT COUNT(*) as count FROM $table ${where != null ? 'WHERE $where' : ''}',
        whereArgs,
      );
      
      return Sqflite.firstIntValue(result) ?? 0;
    } catch (e) {
      developer.log('WebDatabaseService: 计算记录数失败: $e', error: e);
      return 0;
    }
  }

  Future<bool> update(String table, Map<String, dynamic> data) async {
    await _ensureInitialized();
    
    try {
      developer.log('WebDatabaseService: 更新数据，表: $table');
      
      if (!data.containsKey('id') || data['id'] == null) {
        developer.log('WebDatabaseService: 更新失败，缺少ID字段');
        return false;
      }
      
      final id = data['id'];
      final result = await _database!.update(
        table,
        data,
        where: 'id = ?',
        whereArgs: [id],
      );
      return result > 0;
    } catch (e) {
      developer.log('WebDatabaseService: 更新数据失败: $e', error: e);
      return false;
    }
  }

  Future<bool> delete(String table, int id) async {
    await _ensureInitialized();
    
    try {
      developer.log('WebDatabaseService: 删除数据，表: $table，ID: $id');
      
      final result = await _database!.delete(
        table,
        where: 'id = ?',
        whereArgs: [id],
      );
      return result > 0;
    } catch (e) {
      developer.log('WebDatabaseService: 删除数据失败: $e', error: e);
      return false;
    }
  }

  Future<bool> clearTable(String table) async {
    await _ensureInitialized();
    
    try {
      developer.log('WebDatabaseService: 清空表 $table');
      
      await _database!.delete(table);
      return true;
    } catch (e) {
      developer.log('WebDatabaseService: 清空表失败: $e', error: e);
      return false;
    }
  }
  
  /// 删除表中所有数据
  Future<bool> deleteAll(String table) async {
    return clearTable(table);
  }

  // 执行原始SQL查询
  Future<List<Map<String, dynamic>>> rawQuery(String sql, [List<dynamic>? arguments]) async {
    await _ensureInitialized();
    
    try {
      developer.log('WebDatabaseService: 执行原始SQL查询: $sql');
      return await _database!.rawQuery(sql, arguments);
    } catch (e) {
      developer.log('WebDatabaseService: 执行原始SQL查询失败: $e', error: e);
      rethrow;
    }
  }

  Future<void> _ensureInitialized() async {
    if (!_initialized) {
      developer.log('WebDatabaseService: 数据库未初始化，正在初始化');
      await initDatabase();
    }
  }
  
  Future<void> close() async {
    developer.log('WebDatabaseService: 关闭数据库');
    
    await _database?.close();
    
    _database = null;
    _initialized = false;
    
    developer.log('WebDatabaseService: 数据库已关闭');
  }
}